--POUR CONTROLE


define erp='erp_mli2.';
define outils='outils_mli2.';

DROP TABLE se_gam_opera;

CREATE TABLE se_gam_opera AS (SELECT * FROM &erp.gam_opera WHERE 1=2);


--SELECT * FROM UV_PENT_GAMMES_emploi e  /*gamme standard entete*/
--SELECT * FROM UV_PDET_GAMMES d  /*gamme standard ligne*/
--SELECT * FROM  UV_PPT_GAMMES  /*gamme entete article*/
--SELECT * FROM UV_PPT_GAMMES_DETGAM where id like '%P10832741094%' ORDER BY 1/*gamme ligne article */




 INSERT INTO se_gam_opera

    (SELECT ope.id opera_cod,
            'TOUS' prftps_cod,
            SECTION section_cod,
            '0' ilot_cod,
            poste poste_cod,
            0 enreg_id,
            'N' enreg_s,
            des opera_lib,
            commentaire opera_lib2,
            1 tpsprepamach_coef,
            1 tpsprepamo_coef,
            0 tpsreamach_coef,
            1 tpsreamo_coef,
            0 chgtps_typ,
            outil outil_cod,
            thmav thmavant_tps,
            'CH' thmavant_untps_cod,
            thmap thmapres_tps,
            'CH' thmapres_untps_cod,
            NULL manumo_tps,NULL manumo_untps_cod,NULL chgmach_tps,NULL chg_untps_cod,NULL freqmach_tps,NULL freqmach_untps_cod,NULL uofreq_cod,NULL freq_qt,
            CRITERE_UO uocritmod_cod,
            SubStr(uo,InStr(uo,'/')+1,2) uotpsrea_cod,
            REPLACE(SubStr(uo,1,InStr(uo,'/')-1),'MN','M') rea_untps_cod,
            'N' editlib2_ok,
            'N' eclatligcde_ok,
            decode(typetps,'E','E','E') estime_mesure_typ,
            NULL lot_qt,
            nbopreg prepaoper_nb,
            nbop reaoper_nb,
            2 sensuo_ok
            /*tpreg,tpusi*/

        FROM uv_poperations ope
        WHERE EXISTS (SELECT 1 FROM UV_PENT_GAMMES ge,UV_PDET_GAMMES gl,UV_PDET_GAMMES std_lig, UV_PPT_GAMMES_DETGAM art_lig
                      WHERE std_lig.nogamme=ge.id AND art_lig.detgam=std_lig.id
                        AND ge.id=gl.nogamme AND ope.id=gl.operation)
        ) ;


 UPDATE se_gam_opera tf  SET (PRFTPS_COD)=(select PRFTPS_COD FROM &erp.gam_opera f WHERE f.opera_cod=tf.opera_cod );


 UPDATE se_gam_opera tf SET enreg_id=(select enreg_id FROM &erp.gam_opera f WHERE f.opera_cod=tf.opera_cod) ;
 UPDATE se_gam_opera tf SET enreg_id=&outils.enreg_id.NEXTVAL WHERE enreg_id IS NULL OR enreg_id =0;



 CALL uvse_synchroverserp('opera_cod','GAM_OPERA','O') ;

 commit;
 
 
select * from (
SELECT &outils.stragg(source) source,opera_cod, prftps_cod, section_cod, ilot_cod, poste_cod, enreg_id, enreg_s, opera_lib, opera_lib2, tpsprepamach_coef, tpsprepamo_coef, tpsreamach_coef, tpsreamo_coef, chgtps_typ, outil_cod, thmavant_tps, thmavant_untps_cod, thmapres_tps, thmapres_untps_cod, manumo_tps, manumo_untps_cod, chgmach_tps, chg_untps_cod, freqmach_tps, freqmach_untps_cod, uofreq_cod, freq_qt, uocritmod_cod, uotpsrea_cod, rea_untps_cod, editlib2_ok, eclatligcde_ok, estime_mesure_typ, lot_qt, prepaoper_nb, reaoper_nb, sensuo_ok
FROM (
  SELECT 'uv' source ,g.* FROM se_gam_opera g
  UNION ALL
  SELECT 'se' source ,g.* FROM &erp.gam_opera g

  )
GROUP BY opera_cod, prftps_cod, section_cod, ilot_cod, poste_cod, enreg_id, enreg_s, opera_lib, opera_lib2, tpsprepamach_coef, tpsprepamo_coef, tpsreamach_coef, tpsreamo_coef, chgtps_typ, outil_cod, thmavant_tps, thmavant_untps_cod, thmapres_tps, thmapres_untps_cod, manumo_tps, manumo_untps_cod, chgmach_tps, chg_untps_cod, freqmach_tps, freqmach_untps_cod, uofreq_cod, freq_qt, uocritmod_cod, uotpsrea_cod, rea_untps_cod, editlib2_ok, eclatligcde_ok, estime_mesure_typ, lot_qt, prepaoper_nb, reaoper_nb, sensuo_ok
HAVING Count(1)!=2) r
ORDER BY  opera_cod ;